import sqlite3 from 'sqlite3';
import { open } from 'sqlite';

interface Data {
    his: Record<string, any>;
    coins: Record<string, boolean>;
    settings: Record<string, boolean>;
    chats: Record<string, any>;
}

let db;

async function initDb() {
    db = await open({
        filename: './data.db',
        driver: sqlite3.Database
    });

    await db.exec(`
        CREATE TABLE IF NOT EXISTS history (
            key TEXT PRIMARY KEY,
            value TEXT
        );
        CREATE TABLE IF NOT EXISTS coins (
            symbol TEXT,
            wxid TEXT,
            PRIMARY KEY (symbol, wxid)
        );
        CREATE TABLE IF NOT EXISTS settings (
            key TEXT PRIMARY KEY,
            value INTEGER
        );
        CREATE TABLE IF NOT EXISTS chats (
            wxid TEXT,
            content TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        );
    `);
}

await initDb();

export async function getData(): Promise<Data> {
    const coins = await db.all('SELECT symbol, wxid FROM coins');
    const settings = await db.all('SELECT key, value FROM settings');
    const history = await db.all('SELECT key, value FROM history');
    const chats = await db.all('SELECT wxid, content FROM chats');

    const data: Data = {
        his: {},
        coins: {},
        settings: {},
        chats: {}
    };

    for (const row of coins) {
        data.coins[`${row.symbol}:${row.wxid}`] = true;
    }

    for (const row of settings) {
        data.settings[row.key] = Boolean(row.value);
    }

    for (const row of history) {
        data.his[row.key] = JSON.parse(row.value);
    }

    for (const row of chats) {
        if (!data.chats[row.wxid]) {
            data.chats[row.wxid] = [];
        }
        data.chats[row.wxid].push(row.content);
    }

    return data;
}

export async function setData(newData: Data): Promise<void> {
    await db.run('BEGIN TRANSACTION');
    try {
        await db.run('DELETE FROM coins');
        await db.run('DELETE FROM settings');
        await db.run('DELETE FROM history');
        await db.run('DELETE FROM chats');

        for (const [key, value] of Object.entries(newData.coins)) {
            const [symbol, wxid] = key.split(':');
            if (value) {
                await db.run('INSERT INTO coins (symbol, wxid) VALUES (?, ?)', symbol, wxid);
            }
        }

        for (const [key, value] of Object.entries(newData.settings)) {
            await db.run('INSERT INTO settings (key, value) VALUES (?, ?)', key, Number(value));
        }

        for (const [key, value] of Object.entries(newData.his)) {
            await db.run('INSERT INTO history (key, value) VALUES (?, ?)', key, JSON.stringify(value));
        }

        for (const [wxid, messages] of Object.entries(newData.chats)) {
            for (const content of messages as string[]) {
                await db.run('INSERT INTO chats (wxid, content) VALUES (?, ?)', wxid, content);
            }
        }

        await db.run('COMMIT');
    } catch (err) {
        await db.run('ROLLBACK');
        throw err;
    }
}

export async function getCoinChats(symbol: string): Promise<string[]> {
    symbol = symbol.replace('USDT', '').replace('USDC', '');
    const rows = await db.all('SELECT wxid FROM coins WHERE symbol = ?', symbol);
    return rows.map(row => row.wxid);
}

export async function getMyCoins(wxid: string): Promise<string[]> {
    const rows = await db.all('SELECT symbol FROM coins WHERE wxid = ?', wxid);
    return rows.map(row => row.symbol);
}

export async function toggleCoinChat(
    symbol: string,
    wxid: string,
    status: boolean,
): Promise<void> {
    if (status) {
        await db.run('INSERT OR REPLACE INTO coins (symbol, wxid) VALUES (?, ?)', symbol, wxid);
    } else {
        await db.run('DELETE FROM coins WHERE symbol = ? AND wxid = ?', symbol, wxid);
    }
}

export async function getSettings(wxid: string, key: string): Promise<boolean> {
    const row = await db.get('SELECT value FROM settings WHERE key = ?', key + '_' + wxid);
    return Boolean(row?.value);
}

export async function saveSettings(wxid: string, key: string, value: boolean): Promise<void> {
    await db.run(
        'INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)',
        key + '_' + wxid,
        Number(value)
    );
}

export async function toggleRegouStatus(wxid: string, status: string): Promise<string> {
    const value = status.trim() === '开';
    await db.run(
        'INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)',
        'regou_status_' + wxid,
        Number(value)
    );
    return value ? '开' : '关';
}

export async function toggleBinanceStatus(wxid: string, status: string): Promise<string> {
    const value = status.trim() === '开';
    await db.run(
        'INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)',
        'binance_status_' + wxid,
        Number(value)
    );
    return value ? '开' : '关';
}

export async function saveChats(wxid: string, content: string): Promise<void> {
    await db.run('INSERT INTO chats (wxid, content) VALUES (?, ?)', wxid, content);
    const count = await db.get('SELECT COUNT(*) as count FROM chats WHERE wxid = ?', wxid);
    if (count.count > 20) {
        await db.run(
            'DELETE FROM chats WHERE wxid = ? AND rowid IN (SELECT rowid FROM chats WHERE wxid = ? ORDER BY created_at ASC LIMIT ?)',
            wxid,
            wxid,
            count.count - 20
        );
    }
}

export async function getChats(wxid: string): Promise<string[]> {
    const rows = await db.all(
        'SELECT content FROM chats WHERE wxid = ? ORDER BY created_at ASC',
        wxid
    );
    return rows.map(row => row.content);
}
